﻿IF OBJECT_ID ('CHO_ROLES') IS NOT NULL
	DROP TABLE CHO_ROLES
GO

CREATE TABLE CHO_ROLES
(
    ID INT NOT NULL,
    NAME NVARCHAR(50) NOT NULL,
    COMMENT NVARCHAR(2500) NULL,
	ENABLED BIT NOT NULL DEFAULT(1),

    CONSTRAINT PK_CHO_ROLES PRIMARY KEY (ID ASC),
    CONSTRAINT UQ_CHO_ROLES UNIQUE (NAME)
)
GO

IF OBJECT_ID ('CHO_USER_ROLES') IS NOT NULL
	DROP TABLE CHO_USER_ROLES
GO

CREATE TABLE CHO_USER_ROLES
(
    USER_ID int NOT NULL,
    ROLE_ID int NOT NULL,

    CONSTRAINT PK_CHO_USER_ROLES PRIMARY KEY (USER_ID, ROLE_ID),
    CONSTRAINT UQ_CHO_REVERSEPK UNIQUE (ROLE_ID, USER_ID)
)

IF OBJECT_ID ('CHO_ROLE_POLICY') IS NOT NULL
	DROP TABLE CHO_ROLE_POLICY
GO

CREATE TABLE CHO_ROLE_POLICY
(
    ROLE_ID int NOT NULL,
    POLICY_ID int NOT NULL,

    CONSTRAINT PK_CHO_ROLE_POLICY PRIMARY KEY CLUSTERED (ROLE_ID, POLICY_ID)
)

----------------------------------------------------------------

IF OBJECT_ID ('CHO_SELECT_ROLES') IS NOT NULL
	DROP PROC CHO_SELECT_ROLES
GO

CREATE PROCEDURE CHO_SELECT_ROLES
		AS

	SET NOCOUNT ON

	SELECT 
		R.ID, 
		R.NAME,
		R.COMMENT,
		R.ENABLED
	FROM
		CHO_ROLES R
	ORDER BY
		R.NAME
GO


----------------------------------------------------------------

IF OBJECT_ID ('CHO_ADD_OR_UPDATE_ROLE') IS NOT NULL
	DROP PROC CHO_ADD_OR_UPDATE_ROLE
GO

CREATE PROCEDURE CHO_ADD_OR_UPDATE_ROLE
		@NAME VARCHAR(50),
		@COMMENT VARCHAR(2500),
		@ENABLED BIT,
		@ID INT OUTPUT
		AS

	SET NOCOUNT ON

	SELECT @ID = NULL

	SELECT @ID = ID FROM CHO_ROLES WHERE NAME = @NAME

	IF @ID IS NULL
	BEGIN
		INSERT INTO CHO_ROLES (NAME, COMMENT, ENABLED)
			VALUES (@NAME, @COMMENT, @ENABLED)

		SELECT @ID = @@IDENTITY
	END	
	ELSE
	BEGIN
		UPDATE CHO_ROLES
		SET 
			COMMENT = @COMMENT,
			ENABLED = @ENABLED
		WHERE
			NAME = @NAME
	END
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_DELETE_ROLE') IS NOT NULL
	DROP PROC CHO_DELETE_ROLE
GO

CREATE PROCEDURE CHO_DELETE_ROLE
		@ID INT
		AS

	SET NOCOUNT ON

	DELETE FROM CHO_USER_ROLES WHERE ROLE_ID = @ID
	DELETE FROM CHO_ROLE_POLICY WHERE ROLE_ID = @ID
	DELETE FROM CHO_ROLES WHERE ID = @ID
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_SELECT_USER_ROLE') IS NOT NULL
	DROP PROC CHO_SELECT_USER_ROLE
GO

CREATE PROCEDURE CHO_SELECT_USER_ROLE
		@ROLE_ID INT
		AS

	SET NOCOUNT ON

	SELECT 
		R.ID, 
		R.NAME, 
		UR.USER_ID,
		U.NAME as USER_NAME,
		U.FULL_NAME as USER_FULL_NAME
	FROM
		CHO_ROLES R
		JOIN CHO_USER_ROLES UR ON R.ID = UR.ROLE_ID
		JOIN CHO_USERS U ON UR.USER_ID = U.ID
	WHERE
		R.ID = @ROLE_ID
	ORDER BY
		U.FULL_NAME
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_ADD_USER_ROLE') IS NOT NULL
	DROP PROC CHO_ADD_USER_ROLE
GO

CREATE PROCEDURE CHO_ADD_USER_ROLE
		@USER_ID INT,
		@ROLE_ID INT
		AS

	SET NOCOUNT ON

	DELETE CHO_USER_ROLES WHERE USER_ID = @USER_ID --AND ROLE_ID = @ROLE_ID

	IF NOT EXISTS(SELECT 1 FROM CHO_USER_ROLES WHERE USER_ID = @USER_ID AND ROLE_ID = @ROLE_ID)
	BEGIN
		INSERT INTO CHO_USER_ROLES (USER_ID, ROLE_ID)
			VALUES (@USER_ID, @ROLE_ID)
	END	
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_DELETE_USER_ROLE') IS NOT NULL
	DROP PROC CHO_DELETE_USER_ROLE
GO

CREATE PROCEDURE CHO_DELETE_USER_ROLE
		@USER_ID INT,
		@ROLE_ID INT
		AS

	SET NOCOUNT ON

		DELETE FROM CHO_USER_ROLES 
		WHERE 
			USER_ID = @USER_ID
			AND ROLE_ID = @ROLE_ID
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_SELECT_ROLE_POLICY') IS NOT NULL
	DROP PROC CHO_SELECT_ROLE_POLICY
GO

CREATE PROCEDURE CHO_SELECT_ROLE_POLICY
		@ROLE_ID INT
		AS

	SET NOCOUNT ON
	SELECT 
		R.ID, 
		R.NAME, 
		RP.POLICY_ID,
		P.POLICY_DESC
	FROM
		CHO_ROLES R
		JOIN CHO_ROLE_POLICY RP ON R.ID = RP.ROLE_ID
		JOIN CHO_POLICY P ON RP.POLICY_ID = P.POLICY_ID
	WHERE
		R.ID = @ROLE_ID
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_ADD_ROLE_POLICY') IS NOT NULL
	DROP PROC CHO_ADD_ROLE_POLICY
GO

CREATE PROCEDURE CHO_ADD_ROLE_POLICY
		@ROLE_ID INT,
		@POLICY_ID INT
		AS

	SET NOCOUNT ON

	IF NOT EXISTS(SELECT 1 FROM CHO_ROLE_POLICY WHERE ROLE_ID = @ROLE_ID AND POLICY_ID = @POLICY_ID)
	BEGIN
		INSERT INTO CHO_ROLE_POLICY (ROLE_ID, POLICY_ID)
			VALUES (@ROLE_ID, @POLICY_ID)
	END	
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_DELETE_ROLE_POLICY') IS NOT NULL
	DROP PROC CHO_DELETE_ROLE_POLICY
GO

CREATE PROCEDURE CHO_DELETE_ROLE_POLICY
		@ROLE_ID INT,
		@POLICY_ID INT
		AS

	SET NOCOUNT ON

		DELETE FROM CHO_ROLE_POLICY 
		WHERE 
			ROLE_ID = @ROLE_ID
			AND POLICY_ID = @POLICY_ID
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_SELECT_POLICY_FOR_USER') IS NOT NULL
	DROP PROC CHO_SELECT_POLICY_FOR_USER
GO

CREATE PROCEDURE CHO_SELECT_POLICY_FOR_USER
		@USER_NAME VARCHAR(50)
		AS

	SET NOCOUNT ON

	SELECT 
		P.POLICY_ID, P.POLICY_DESC
	FROM
		CHO_USERS U,
		CHO_USER_ROLES UR,
		CHO_ROLE_POLICY RP,
		CHO_POLICY P,
		CHO_ROLES R
	WHERE
		U.NAME = @USER_NAME
		AND U.ID = UR.USER_ID
		AND UR.ROLE_ID = RP.ROLE_ID
		AND RP.POLICY_ID = P.POLICY_ID
		AND UR.ROLE_ID = R.ID
		AND R.ENABLED = 1
		AND U.ENABLED = 1
GO

----------------------------------------------------------------

INSERT INTO CHO_USERS (NAME, FULL_NAME, PASSWORD, ENABLED)
VALUES ('XBBKP28', 'Raj Nagalingam', NULL, 1)
GO

INSERT INTO CHO_USER_ROLES (USER_ID, ROLE_ID)
VALUES (1, 1)
GO

INSERT INTO CHO_ROLES (NAME, ENABLED)
VALUES ('ADMIN', 1)
GO

INSERT INTO CHO_ROLES (NAME, ENABLED)
VALUES ('READ_ONLY', 1)
GO

INSERT INTO CHO_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 1)
GO

INSERT INTO CHO_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 2)
GO

INSERT INTO CHO_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 3)
GO

INSERT INTO CHO_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 4)
GO

INSERT INTO CHO_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 5)
GO

INSERT INTO CHO_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 6)
GO

INSERT INTO CHO_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 7)
GO

INSERT INTO CHO_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 8)
GO

INSERT INTO CHO_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 9)
GO

INSERT INTO CHO_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 10)
GO

INSERT INTO CHO_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 11)
GO

INSERT INTO CHO_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 13)
GO

INSERT INTO CHO_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 14)
GO

INSERT INTO CHO_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 15)
GO

